---
sidebar_label: Tables basics
sidebar_position: 1
description: GraphQL over BigQuery tables in Hasura
keywords:
  - hasura
  - docs
  - BigQuery
  - schema
  - tables
---

import GraphiQLIDE from '@site/src/components/GraphiQLIDE';
import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# BigQuery: Tables Basics

## Introduction

Adding tables allows you to define the GraphQL types of your schema including their corresponding fields.

## Creating tables

Let's say we want to create two simple tables for `articles` and `author` schema:

```sql
authors (
    `id` INT64,
    `name` STRING
);

articles (
    `id` INT64,
    `title` STRING,
    `body` STRING,
    `author_id` INT64,
    `is_published` BOOL,
    `published_on` DATETIME,
    `rating` INT64
);

```

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Open the Hasura Console and head to the `Data` tab and click on the button on the left side bar to open up an interface
to create tables.

For example, here is the schema for the `articles` table in this interface:

<Thumbnail src="/img/schema/create-table-graphql-bigquery.png" alt="Schema for an article table" />

</TabItem>
<TabItem value="cli" label="CLI">

1.  [Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
    following SQL statement to the `up.sql` file:

    ```sql
    CREATE TABLE `bigquery.authors` (
        `id` INT64,
        `name` STRING
    );

    CREATE TABLE `bigquery.articles` (
        `id` INT64,
        `title` STRING,
        `body` STRING,
        `author_id` INT64,
        `is_published` BOOL,
        `published_on` DATETIME,
        `rating` INT64
    );
    ```

2.  Add the following statement to the `down.sql` file in case you need to
    [roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations)` the above statement:

    ```sql
    DROP TABLE `hasura.author`;
    DROP TABLE `hasura.article`;
    ```

3.  Apply the migration by running:

    ```bash
    hasura migrate apply
    ```

</TabItem>
<TabItem value="api" label="API">

You can create a table by making an API call to the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql)
schema API:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "bigquery_run_sql",
    "args": {
        "source": "<db_name>",
        "sql": "CREATE TABLE `bigquery.author` (`id` INT64,`name` STRING);CREATE TABLE `bigquery.article` (`id` INT64,`title` STRING,`body` STRING,`author_id` INT64,`is_published` BOOL,`published_on` DATETIME,`rating` INT64);"
    }
}
```

</TabItem>
</Tabs>

## Tracking tables

Tables can be present in the underlying BigQuery database without being exposed over the GraphQL API. In order to expose
a table over the GraphQL API, it needs to be **tracked**.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

When a table is created via the Hasura Console, it gets tracked by default.

You can track any existing tables in your database from the `Data -> Schema` page:

<Thumbnail src="/img/schema/schema-track-tables-bigquery.png" alt="Track table" />

</TabItem>
<TabItem value="cli" label="CLI">

1.  To track the table and expose it over the GraphQL API, edit the `tables.yaml` file in the `metadata` directory as
    follows:

    ```yaml {4-6}
    - table:
        dataset: bigquery
        name: authors
    - table:
        dataset: bigquery
        name: articles
    ```

2.  Apply the metadata by running:

    ```bash
    hasura metadata apply
    ```

</TabItem>
<TabItem value="api" label="API">

To track the table and expose it over the GraphQL API, make the following API call to the
[bigquery_track_table](/api-reference/metadata-api/table-view.mdx#metadata-bigquery-track-table) metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bigquery_track_table",
  "args": {
    "source": "<db_name>",
    "table": {
        "dataset": "bigquery",
        "name": "articles"
    }
  }
}
```

</TabItem>
</Tabs>

## Generated GraphQL schema types

As soon as a table is created and tracked, the corresponding GraphQL schema types and query/mutation fields will be
automatically generated.

The following object type is generated for the `articles` table we just created and tracked:

```graphql
# Object type
type Articles {
  id: Int
  title: String
  body: String
  author_id: Int
  is_published: Boolean
  published_on: Datetime
  rating: Int
}
```

Let's analyze the above type:

- `Articles` is the name of the type
- `id`, `title`, `body`, and so on are fields of the `Articles` type
- `Int` and `String`, `Boolean` and `Datetime` are types that fields can have

The following query/mutation fields are generated for the `articles` table we just created and tracked:

```graphql
# Query field: fetch data from the table: "bigquery.articles"
bigquery_articles(
    distinct_on: [bigquery_articles_select_column!]
    limit: Int
    offset: Int
    order_by: [bigquery_articles_order_by!]
    where: bigquery_articles_bool_exp
): [bigquery_articles!]!

# Query field: fetch aggregated fields from the table: "bigquery.articles"
bigquery_articles_aggregate(
    distinct_on: [bigquery_articles_select_column!]
    limit: Int
    offset: Int
    order_by: [bigquery_articles_order_by!]
    where: bigquery_articles_bool_exp
): bigquery_articles_aggregate!
```

These auto-generated fields will allow you to query data in the table.

See the [query](/api-reference/graphql-api/query.mdx) API reference for the full specifications.

### GraphQL types documentation

Hasura automatically picks up any comments that might have been added to your tables and columns and adds them as
GraphQL descriptions of the auto-generated types and fields.

## Try out basic GraphQL requests

At this point, you should be able to try out basic GraphQL queries/mutations on the newly created tables from the `API`
tab in the Console. _(You may want to add some sample data into the tables first)_

- Query all rows in the `articles` table:

  <GraphiQLIDE
    query={`query {
    bigquery_articles {
      author_id
      id
      title
    }
  }`}
    response={`{
  "data": {
    "bigquery_articles": [
      {
        "author_id": "1",
        "id": "2",
        "title": "veniam"
      },
      {
        "author_id": "2",
        "id": "3",
        "title": "nostrud"
      },
      {
        "author_id": "3",
        "id": "4",
        "title": "cillum nostrud"
      },
      {
        "author_id": "3",
        "id": "1",
        "title": "dolore"
      },
      {
        "author_id": "4",
        "id": "5",
        "title": "culpa qui officia"
      }
    ]
  }
}`}
  />
